package com.om39a.plm.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.om39a.plm.parser.PLMUserCondition;
import com.om39a.plm.parser.PLMThreadPost;
import com.om39a.plm.parser.PLMUser;

/**
 * @author om39a
 * 
 */
public class PLMXlsUtils {

	List<String> tableHeader = new ArrayList<String>();

	public static PLMXlsUtils getWriteThreadDataInstance() {
		PLMXlsUtils plmXlsUtils = new PLMXlsUtils("THREAD_DATA");
		return plmXlsUtils;
	}

	public static PLMXlsUtils getWriteUserDataInstance() {
		PLMXlsUtils plmXlsUtils = new PLMXlsUtils("USER_DATA");
		return plmXlsUtils;
	}

	public static PLMXlsUtils getWriteUserConditionsDataInstance() {
		PLMXlsUtils plmXlsUtils = new PLMXlsUtils("USER_CONDITIONS");
		return plmXlsUtils;
	}

	public PLMXlsUtils(String headerType) {
		// Table headers
		if ("THREAD_DATA".equalsIgnoreCase(headerType)) {
			tableHeader.add("Forum ID");
			tableHeader.add("Forum Name");
			tableHeader.add("Thread ID");
			tableHeader.add("Thread Name");
			tableHeader.add("Thread Created By");
			tableHeader.add("Post ID");
			tableHeader.add("Post Created By");
			tableHeader.add("Post Created Date");
			tableHeader.add("Post Content");
			tableHeader.add("Post Helpfull Markers");
			tableHeader.add("Tags");
			tableHeader.add("Treatement URL");
			tableHeader.add("Other Content URL");
			creatTemplate("template1.xls");
		} else if ("USER_DATA".equalsIgnoreCase(headerType)) {
			tableHeader.add("User ID");
			tableHeader.add("User Name");
			tableHeader.add("User Type");
			tableHeader.add("Gender");
			tableHeader.add("Age");
			tableHeader.add("Place");
			tableHeader.add("Date Joined");
			tableHeader.add("Posts");
			tableHeader.add("Helpfull Markers");
			tableHeader.add("Data Quality Stars");
			tableHeader.add("Followers");
			tableHeader.add("Following");
			creatTemplate("template2.xls");
		} else if ("USER_CONDITIONS".equalsIgnoreCase(headerType)) {
			tableHeader.add("User ID");
			tableHeader.add("User Name");
			tableHeader.add("Condition Name");
			tableHeader.add("Condition Type");
			tableHeader.add("Diagnosi Date");
			tableHeader.add("First Symptome");
			tableHeader.add("Stop Date");
			tableHeader.add("Stop reason");
			tableHeader.add("Occured Date");
			creatTemplate("template3.xls");
		}
	}

	public void writeThreadDataToXlsFile(PLMThreadPost thread)
			throws IOException {

		InputStream inp = new FileInputStream("template1.xls");

		Workbook wb = new HSSFWorkbook(inp);
		Sheet sheet = wb.getSheetAt(0);

		Row row = sheet.createRow((short) (sheet.getLastRowNum() + 1));

		Cell c = null;
		CreationHelper createHelper = wb.getCreationHelper();

		// Forum ID
		c = row.createCell(0);
		c.setCellValue(createHelper.createRichTextString("----"));

		// Forum Name
		c = row.createCell(1);
		c.setCellValue(createHelper.createRichTextString(thread
				.getParentForum()));

		// Thread ID
		c = row.createCell(2);
		c.setCellValue(createHelper.createRichTextString(thread.getThreadId()));

		// Thread Name
		c = row.createCell(3);
		c.setCellValue(createHelper.createRichTextString(thread
				.getThreadTitle()));

		// Thread Created by
		c = row.createCell(4);
		c.setCellValue(createHelper.createRichTextString(thread
				.getThreadCreatedBy()));

		// Post ID
		c = row.createCell(5);
		c.setCellValue(createHelper.createRichTextString(thread.getPostId()));

		// Post Created By
		c = row.createCell(6);
		c.setCellValue(createHelper.createRichTextString(thread
				.getPostCreatedBy()));

		// Post Created date
		c = row.createCell(7);
		c.setCellValue(createHelper.createRichTextString(thread
				.getPostCreatedDate()));

		// Post Content
		c = row.createCell(8);
		c.setCellValue(createHelper.createRichTextString(thread
				.getPostContent()));

		// Post Helpfull markers
		c = row.createCell(9);
		c.setCellValue(createHelper.createRichTextString(thread
				.getPostHelpFullMarks()));

		// Post Tags
		c = row.createCell(10);
		StringBuffer tags = new StringBuffer();
		for (String tag : thread.getThreadtags()) {
			tags.append(tag);
			tags.append(",");
		}
		c.setCellValue(createHelper.createRichTextString(tags.toString()));

		// Treatment URLs
		c = row.createCell(11);
		StringBuffer treatmentUrls = new StringBuffer();
		for (String treatmentUrl : thread.getTreatmentUrls()) {
			treatmentUrls.append(treatmentUrl);
			treatmentUrls.append(",");
		}
		c.setCellValue(createHelper.createRichTextString(treatmentUrls
				.toString()));

		// Other content URLs
		c = row.createCell(12);
		StringBuffer contentUrls = new StringBuffer();
		for (String contentUrl : thread.getContentUrls()) {
			tags.append(contentUrl);
			tags.append(",");
		}
		c.setCellValue(createHelper.createRichTextString(contentUrls.toString()));

		FileOutputStream fileOut = new FileOutputStream("template1.xls");
		wb.write(fileOut);
		fileOut.close();

	}

	public void writeUserDataToXlsFile(PLMUser plmUser) throws IOException {

		InputStream inp = new FileInputStream("template2.xls");

		Workbook wb = new HSSFWorkbook(inp);
		Sheet sheet = wb.getSheetAt(0);

		Row row = sheet.createRow((short) (sheet.getLastRowNum() + 1));

		Cell c = null;
		CreationHelper createHelper = wb.getCreationHelper();

		// User ID
		c = row.createCell(0);
		c.setCellValue(createHelper.createRichTextString("----"));

		// User Name
		c = row.createCell(1);
		c.setCellValue(createHelper.createRichTextString(plmUser.getUserName()));

		// User Type
		c = row.createCell(2);
		c.setCellValue(createHelper.createRichTextString(plmUser.getUserType()));

		// Gender
		c = row.createCell(3);
		c.setCellValue(createHelper.createRichTextString(plmUser.getGender()));

		// Age
		c = row.createCell(4);
		c.setCellValue(createHelper.createRichTextString(plmUser.getAge()));

		// Place
		c = row.createCell(5);
		c.setCellValue(createHelper.createRichTextString(plmUser.getPlace()));

		// Data Joined
		c = row.createCell(6);
		c.setCellValue(createHelper.createRichTextString(plmUser
				.getDateJoined()));

		// Post Count
		c = row.createCell(7);
		c.setCellValue(createHelper.createRichTextString(plmUser.getNoOfPosts()));

		// Helpful markers count
		c = row.createCell(8);
		c.setCellValue(createHelper.createRichTextString(plmUser
				.getNoOfHelpfulMarks()));

		// Data quality Stars
		c = row.createCell(9);
		c.setCellValue(createHelper.createRichTextString(plmUser
				.getDataQualityStars()));

		// Followers
		c = row.createCell(10);
		StringBuffer followers = new StringBuffer();
		for (String tag : plmUser.getFollowers()) {
			followers.append(tag);
			followers.append(",");
		}
		c.setCellValue(createHelper.createRichTextString(followers.toString()));

		// Following
		c = row.createCell(11);
		StringBuffer usersFollowing = new StringBuffer();
		for (String following : plmUser.getFollowing()) {
			usersFollowing.append(following);
			usersFollowing.append(",");
		}
		c.setCellValue(createHelper.createRichTextString(usersFollowing
				.toString()));

		FileOutputStream fileOut = new FileOutputStream("template2.xls");
		wb.write(fileOut);
		fileOut.close();

	}

	public void writeUserConditionsToXlsFile(PLMUserCondition plmUserCondition)
			throws IOException {

		InputStream inp = new FileInputStream("template3.xls");

		Workbook wb = new HSSFWorkbook(inp);
		Sheet sheet = wb.getSheetAt(0);

		Row row = sheet.createRow((short) (sheet.getLastRowNum() + 1));

		Cell c = null;
		CreationHelper createHelper = wb.getCreationHelper();

		// User ID
		c = row.createCell(0);
		c.setCellValue(createHelper.createRichTextString(plmUserCondition
				.getUserID()));

		// User Name
		c = row.createCell(1);
		c.setCellValue(createHelper.createRichTextString(plmUserCondition
				.getUserID()));

		// User Type
		c = row.createCell(2);
		c.setCellValue(createHelper.createRichTextString(plmUserCondition
				.getConditionName()));

		// Gender
		c = row.createCell(3);
		c.setCellValue(createHelper.createRichTextString(plmUserCondition
				.isPrimaryCondition() ? "PRMARY" : "SECONDARY"));

		// Age
		c = row.createCell(4);
		c.setCellValue(createHelper.createRichTextString(plmUserCondition
				.getDiagnosiDate()));

		// Place
		c = row.createCell(5);
		c.setCellValue(createHelper.createRichTextString(plmUserCondition
				.getFirstSymptomDate()));

		// Data Joined
		c = row.createCell(6);
		c.setCellValue(createHelper.createRichTextString(plmUserCondition
				.getStopedDate()));

		// Post Count
		c = row.createCell(7);
		c.setCellValue(createHelper.createRichTextString(plmUserCondition
				.getStopedReason()));

		// Helpful markers count
		c = row.createCell(8);
		c.setCellValue(createHelper.createRichTextString(plmUserCondition
				.getOccured()));

		FileOutputStream fileOut = new FileOutputStream("template3.xls");
		wb.write(fileOut);
		fileOut.close();
	}

	/**
	 * To create a empty sheet where all the data will be placed later on
	 */
	private void creatTemplate(String fileName) {

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("Thread posts");
		int rownum = 0;
		int cellnum = 0;
		Row row = sheet.createRow(rownum++);
		for (String key : tableHeader) {
			Cell cell = row.createCell(cellnum++);
			cell.setCellValue(key);
		}

		try {
			FileOutputStream out = new FileOutputStream(new File(fileName));
			workbook.write(out);
			out.close();
			System.out.println("Excel template written successfully..");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public void main(String[] args) throws IOException {
		PLMXlsUtils plmXlsUtils = PLMXlsUtils.getWriteThreadDataInstance();
		// plmXlsUtils.writeToXlsFile(null);
	}
}
